const express = require('express')
const Router = express.Router()
const sql = require('./sql/sql')
const jwt = require('./token/token')
const dblink = require('./sql/dblink')
const { error } = require('console')

Router.get('/banner', (req, res) => {
    const sqlmethod = 'select * from banner'
    sql(sqlmethod, (error, data) => {
        if (error) {
            console.log(error);
        } else {
            res.send({
                status: 200,
                msg: 'Banner获取成功',
                data
            })
        }
    })
})

Router.post('/login', (req, res) => {

    const {
        body
    } = req

    const params = [
        body.username,
        body.password
    ]
    sql('login', params).then(result => {

        if (result.length > 0) {
            const {
                coupon,
                balance,
                intergral,
                mobel
            } = result[0]
            // console.log(result[0].id);
            // console.log(result[0].coupon);
            // console.log(result[0].balance);
            // console.log(result[0].intergral);
            res.send({
                code: 200,
                token: jwt.sign(body),
                msg: `${body.username}登录成功!`,
                user: body.username,
                coupon,
                balance,
                intergral,
                mobel
            })
        } else(
            res.send({
                code: 500,
                msg: '用户名或密码错误'
            })
        )
    })


})

/* 不需要TOKEN接口 */
Router.get('/list', (req, res) => {

    sql('goods').then(data => {
        res.send(data)
    })

})


/* 需要TOKEN接口 */
Router.get('/goods', (req, res) => {

    res.send({
        status: 200,
        msg: '恭喜请求成功'
    })

})

/* 常用链接地址 */
Router.get('/link', (req, res) => {
    dblink().then(data => {
        res.send(data)
    })
})

/* 添加链接 */
Router.get('/editlink', (req, res) => {
    const {
        user
    } = req.query
    let {
        username,
        password
    } = jwt.decode(user)
    let params = [username, password]
    sql('login', params).then(result => {
        if (result.length > 0) {
            res.send({
                status: 200
            })
        }
    })
})

Router.post('/addlink', (req, res) => {

    const {
        link,
        addr,
        token
    } = req.body

    let {
        username,
        password
    } = jwt.decode(token)
    let params = [username, password]
    sql('login', params).then(result => {
        if (result.length < 1) {
            res.send({
                status: 500,
                msg: '非法用户'
            })
        } else {
            /* 将链接插入 */
            const params = [addr, link]
            // const sqlmethod = 'insert into link set addr=? and link=?'
            const sqlmethod = 'insert into link(addr,link) value(?,?)'
            dblink(sqlmethod, params).then(result => {
                if (result.affectedRows < 0) {
                    res.send({
                        status: 500,
                        msg: '填加失败'
                    })
                } else {
                    res.send({
                        status: 200,
                        msg: link + ' 添加成功'
                    })
                }
            })
        }
    })
})
/* channel */
Router.get('/channel', (req, res) => {

    
    const sqlmethod = 'select * from nideshop_channel'
    sql(sqlmethod, (error, data) => {
        if (error) {
            console.log('数据库请求失败');
            return error
        } else {
            res.send({
                status: 200,
                data,
                msg: '请求channel成功'
            })
        }

    })
})



/* 获取分类 */
Router.get('/category', (req, res) => {
    const {
        categoryid
    } = req.query
    const sqlmethod = 'select * from nideshop_category where parent_id=?'
    sql(sqlmethod, categoryid).then(result => {
        res.send({
            status: 200,
            data: result,
            msg: '请求成功'
        })
    })
})

/* mall项目 */
/* 获取分类 */
Router.get('/categorylist', (req, res) => {
    const sqlmethod = 'select * from nideshop_category'
    sql(sqlmethod, (err, result) => {
        if (err) {
            console.log('分类获取失败');
            return
        } else {
            res.send({
                status: 200,
                result,
                msg: '分类获取成功'
            })
        }
    })
})


/* 从分类获取产品 */
/* 并且获取分类属性 */
Router.get('/reqgoods',async(req, res) => {
    // 配置默认项

    let {category,goodskey,pagenum,pagesize,order,l1_category} = req.query

    pagenum = pagenum || 1
    pagesize = pagesize || 10
    order = order || '1:desc'

    let pageSet = ` limit ${pagesize} offset ${(pagenum-1)*pagesize}`

    // 获取总产品数量
    let totalMethods = "SELECT COUNT(*) FROM nideshop_goods " //SELECT COUNT(*) FROM nideshop_goods 

    let orderOption ={
        1:'id',
        2:'retail_price'
    }

    // 排序
    let orderMethods = ` order by ${orderOption[order.split(':')[0]]} ${order.split(':')[1]} `

    // 需要返回的变量
    let goods,total,sortList;

    let baseMethods = "SELECT * FROM nideshop_goods"
    // 模糊查询
    let goodsMethods = ` name like ? `
    // 分类查询
    let categoryMethods= `category_id=? `

            // let testMethods = `SELECT COUNT(*) FROM nideshop_goods where category_id=1008009;SELECT * FROM nideshop_goods where category_id=1008009 ${orderMethods} ${pageSet}`
          // 获取总条数
        // let totalMtd = totalMethods + " WHERE " + condition
        // let totalAccpet = await sql(totalMtd,payload)
        // total = totalAccpet[0]['COUNT(*)'];
        // let test = "SELECT count(*) FROM nideshop_goods where name like '%c%'" 
    
    async function singleCondition (payload,condition){

        let totalM = totalMethods + " WHERE " + condition ;
        let goodsM=  baseMethods + " WHERE " + condition + orderMethods + pageSet;
        let methods = `${totalM} ; ${goodsM}`
        // let test = "SELECT count(*) FROM nideshop_goods where name like '%1%' " 
        goods = await sql(methods,[payload,payload])
       
        total = [...goods[0]]
        total =total[0]["COUNT(*)"]
        goods = [...goods[1]]
     
        if(!category){
           
            res.send({
                status: 200,
                sortList,
                goods,
                total,
                order,
                pagesize,
                pagenum,
                
                msg: '获取产品列表和分类属性成功'
           })
        }
        
    }

            // 查询参数
           async function bothSearch(keyword,category){
                let key = `%${keyword}%`
                let options = [category,key,category,key]
                let totalM= totalMethods + " WHERE " + categoryMethods + ' AND '+ goodsMethods
                let goodsM =baseMethods+' WHERE '+ categoryMethods +' AND '+ goodsMethods + orderMethods + pageSet;
                let allParamsMethods = `${totalM};${goodsM}`
                // let testMethods = "SELECT COUNT(*) FROM goods;SELECT * FROM goods where category_id=1008009 AND name like ?"
                goods =await sql(allParamsMethods,options)
                // let totalMtd =totalMethods + ' WHERE '+ categoryMethods +' AND '+ goodsMethods +pageSet;
                // total = await sql(totalMtd,[category,goodskey])
                // total =total[0]['COUNT(*)'];
                
                total = [...goods[0]]
                total = total[0]['COUNT(*)']
                goods = [...goods[1]]
                
                return {total,goods}
            }
           

    //   并行查询 
    if(goodskey && category){
        await bothSearch(goodskey,category)
       
              getGoodsAttribute()
               
    }else if(category && !goodskey){
        
        await singleCondition(category,categoryMethods)
        
             getGoodsAttribute()
             
    }else if(l1_category){
        
        // L1数据库里找分类
        // let testmethods= "SELECT * FROM nideshop_goods WHERE category_id in('1005007','1032000')"
        // let rs =await sql(testmethods)

        let l1_methods = `SELECT * FROM nideshop_category WHERE parent_id=?`
       
        let l2 = await sql(l1_methods,l1_category)
        
        var l2Str = []
        let goods;
        for(item in l2){
            let l2Id =l2[item].id
            l2Str.push(l2Id)
             // 循环获得L2里的产品
        }

        if(goodskey){
            let categoryM = `category_id in (?)`
            let key = `%${goodskey}%`
            let options = [l2Str,key,l2Str,key]
            let totalM= totalMethods + " WHERE " + categoryM + ' AND '+ goodsMethods
            let goodsM =baseMethods+' WHERE '+ categoryM +' AND '+ goodsMethods + orderMethods + pageSet;
            let allParamsMethods = `${totalM};${goodsM}`
            // let testMethods = "SELECT COUNT(*) FROM goods;SELECT * FROM goods where category_id=1008009 AND name like ?"
            goods =await sql(allParamsMethods,options)
            // let totalMtd =totalMethods + ' WHERE '+ categoryMethods +' AND '+ goodsMethods +pageSet;
            // total = await sql(totalMtd,[category,goodskey])
            // total =total[0]['COUNT(*)'];
            
            total = [...goods[0]]
            total = total[0]['COUNT(*)']

            goods = [...goods[1]]
            
            res.send({
                status:200,
                goods,
                total,
                pagesize,
                pagenum,
                // sortList
            })
        
            
         }else{

             let payload = l2Str
             let condition = `category_id in (?)`
             let totalM = totalMethods + " WHERE " + condition ;
             let goodsM=  baseMethods + " WHERE " + condition + orderMethods + pageSet;
             let methods = `${totalM} ; ${goodsM}`
             // let test = "SELECT count(*) FROM nideshop_goods where name like '%1%' " 
             goods = await sql(methods,[payload,payload])
             
             total = [...goods[0]]
             total = total[0]['COUNT(*)']

             goods = [...goods[1]]
             
             res.send({
                status:200,
                goods,
                total,
                pagesize,
                pagenum,
                
            })
         }


        // 如果 有关键字 就在每一个分类里找这个关键字,并且将总数相加
        
    }else if(goodskey && !category){
        
        // let goodsMethods = ` name like ? `
        let goodsOption = `%${goodskey}%`
        // let methods = baseMethods + " WHERE " + goodsMethods + pageSet
        singleCondition(goodsOption,goodsMethods)

    }else{

    }

   async function getGoodsAttribute(){
            
        let attributeMethods = "select * from nideshop_attribute where attribute_category_id=?"
   
        let goodsAttribute = [] //分类属性值
    
        let catagoryParams = 'select * from nideshop_goods_attribute where attribute_id=? limit 1' //截取5段数据
         // 通过分类,去查找分类属性名=======获取属性
        let categoryAttribute = await sql(attributeMethods, category)

        
        for (i in categoryAttribute) {
         
            let attribute = await sql(catagoryParams, categoryAttribute[i].id)
      
            goodsAttribute[i] = attribute
           
         }
        if(category && categoryAttribute){
            
            res.send({
                status: 200,
                goods,
                categoryAttribute,
                goodsAttribute,
                order,
                total,
               
                pagesize,
                pagenum,
                msg: '获取产品列表和分类属性成功'
        })
        }else{
            res.send({
                status:500,
                msg:'获取分类失败'
            })
        }
     }


    //  从产品ID中获取 产品属性与
    async function acceptGoods(goods){
        //  收集所有Id
         let goodsIdList =''
        if(!goods){
           res.send({
               status:500,
               msg:'没到收到产品数据'
           })
        }

        goods.forEach( item =>{
            goodsIdList += ',' +item.id
            
        })
        goodsIdList = goodsIdList.replace(',','')
        
        let syntax = `SELECT * FROM nideshop_goods_attribute WHERE goods_id in (${goodsIdList})`
        //获取全有产品属性
        let allGoodsAttribute = await sql(syntax)
        
        // 做一个对照序号数组
        let nameList = []


         // 分析数组中是否有相同分类
        for(let i = 0;i < allGoodsAttribute.length; i++){
            let {attribute_id} = allGoodsAttribute[i]
            if( nameList.indexOf(attribute_id) == -1){
                nameList.push(attribute_id)
                // 获取产品分类名

                let sqlmethod = `select * from nideshop_attribute where id=${attribute_id}`
                let name = await sql(sqlmethod)
                sortList.push({[attribute_id]:[allGoodsAttribute[i]],name:name[0].name})
                
        }else{
            let place = nameList.indexOf(attribute_id)
            sortList[place][allGoodsAttribute[i].attribute_id].push(allGoodsAttribute[i])
        }
        }

        res.send({
            status: 200,
            goods,
            total,
            order,
            pagesize,
            pagenum,
            sortList,
            msg: '获取产品列表和分类属性成功'
       })

     }
    

})

/* 获取商品详情 */
Router.get('/shopgoods', (req, res) => {
    let {
        goods
    } = req.query
    const sqlmethod = 'select * from nideshop_goods where id=?'
    sql(sqlmethod, goods).then(result => {
        if (result.length <= 0) {
            res.send({
                status: 500,
                msg: '没找到商品'
            })
        } else {
            res.send({
                status: 200,
                result,
                msg: '找到商品'
            })

        }
    })

})


//通过ID查找产品
Router.get('/sku/:skuId', async (req,res)=>{
    let {skuId} = req.params
    let syntax = "select * from nideshop_goods where id=?" 

    let attributeSyntax = "SELECT * FROM nideshop_goods_attribute WHERE goods_id=?"
    let sku =  sql(syntax,skuId)
    let attribute = sql(attributeSyntax,skuId)
    let result = await Promise.all([sku,attribute])
    if(result.length > 0){
              res.send({
                     status:200,
                     result,
                     msg:'sku查询成功'
                 })
             }else{
                 res.send({
                     status:401,
                     msg:'sku查询失败'
                 })
            
                }
})
 
            //修改商品数量
// 预处理 shopCart 
Router.post('/readyCart',async (req,res)=>{
    let {skuId,skuNum} = req.body
    let tempId = req.headers.tempid
    // 获得SKU 数量
    let syntax = `select * from nideshop_goods where id=?`
    let result =  await sql(syntax,skuId)
    if(result.length > 0){
        let RowDataPacket = [ ...result]
        if(RowDataPacket[0].sell_volume - skuNum > 0){
            var searchRes;
            // 先查询数据库内是否有重复
            let searchSyntax = "select * from nideshop_shopcart where uuid=? and skuid=?"
            let total = await sql(searchSyntax,[tempId,skuId])
          
            // 合并数据
            let sqltotal = [ ...total]
            // sqltotal[0].sell_volume

             // 储存到SQL里
            if(total.length > 0){
                let id = sqltotal[0].id
                let currentNum = sqltotal[0].skunum + parseInt(skuNum)
               
                let updataSyntax = "UPDATE nideshop_shopcart SET skunum=? WHERE id=?"
                // UPDATE `ego`.`nideshop_shopcart` SET `skunum` = 10 WHERE `id` = 11
                searchRes = await sql(updataSyntax,[currentNum,id])
                
            }else{
                let syntax = "INSERT INTO nideshop_shopcart (uuid,skuid,skunum) value(?,?,?)"
                searchRes = await sql(syntax,[tempId,skuId,skuNum])
            }
            
            if(searchRes.affectedRows > 0){

                res.send({
                    status:200,
                    msg:'加入购物车成功'
                })
            }else{
                return new error('数据库写入失败')

            }
        }else{
            res.send({
                status:500,
                msg:'库存数量不足'
            })
        }
    }
})

// 购物车 带UUID预处理
Router.get('/cart',async (req,res)=>{
    let tempId = req.headers.tempid
    // 查询UUID
    let syntax = "select * from nideshop_shopcart where uuid=?"
    let result = await sql(syntax,tempId)
    if(result.length > 0){

       function addList(skuid){
            let syntax = "select * from nideshop_goods where id=?"
            let goods = sql(syntax,skuid.skuid)
            return goods
        }

        for(let i = 0; result.length > i; i++){
           let goods = await addList(result[i])
            result[i]['goods'] = goods[0]
        }


        res.send({
            status:200,
            data:result,
            msg:'购物车查询成功'
        })
    }else{
        res.send({
            status:200,
            msg:'没有购物车商品',
            data:[]
        })
    }
   


    
})


module.exports = Router;